152

Beginner’s Guide to Code Algorithms

152

STEP 4 continued (2)

Load the US maps.

  Do

    On Error GoTo continue

    Set fileopenMapWb =​ Workbooks.Open(filepathMap)

    Exit Do

continue:

    filepathMapFuture =​ filepathMap

    filepathMap =​ Application.GetOpenFilename

    MsgBox (“Please identify the USAMapCovid.xlsx file you saved”)

    Set fileopenMapWb =​ Workbooks.Open(filepathMap)

    fileopenMapWb.SaveAs Filename:=​filepathMapFuture

    Loop Until filepathMap <> False

11.7  BUILDING THE MAP

The US maps is a collection of 50 different shapes in MSDraw—​each representing one

state. The name of each shape is the same as the name of the state. These are all grouped

together in a group called “Group 57” in the code (step 7) —​you can change this name

to your group as required in the code. This group needs to be in an Excel file called

USAMapCovid.xlsx, in a sheet called “MAPS”. The code above does provide for other

names for this file, but this collection of 50 images is necessary to show the US map

with the cool selection colors. Such groups are available through online vendors, or you

can draw your own using standard drawing tools. (Hint: paste a picture of the US map

and draw over it using MSDraw Freeform Shape tool that looks like a scribble:

STEP 4 continued (3)

Populate the data sheet in the format of the “Any State” sheet mentioned above.

  Set fileopenWb =​ Workbooks.Open(filepath)

  ‘Workbooks(FileToValidate).Activate

  Set fileopenWs =​ fileopenWb.Sheets(SheetToValidate)

  ‘thisWb is the analysis workbook

  thisWs.Range(“A2:A1000”, “B1:JZ1000”).ClearContents

  thisWsGraphs.Activate

  For Each Shp In ActiveSheet.Shapes

    Shp.Delete

  Next Shp

  NumberOfRows =​ fileopenWs.Cells(Rows.Count, 1).End(xlUp).Row

  NumberOfCols =​ fileopenWs.Cells(1, Columns.Count).End(xlToLeft).Column

  ‘thisWs.Cells(1, 1) =​ 1

  ‘fileopenWs.Cells(1, 1) =​ 1

 

  NumberOfStates =​ thisWb.Sheets(“States”).Cells(Rows.Count, 1).End(xlUp).

Row -​ 1 ‘compensating for header row

  fileopenWs.Activate

  j =​ 1

  k =​ 0